PostgreSQL

• smallint

• integer

• bigint

• decimal

• numeric

• real

• double precision

• smallserial

• serial

• bigserial

• money

• char, character

• character varying, varchar

• text

• bytea

• boolean

• date

• timestamp [without time zone]

• timestamp with time zone

• time [without time zone]

• time with time zone

• interval

• point

• line

• lseg

• box

• path

• polygon

• circle

• inet

• cidr

• macaddr

• macaddr8

• bit

• bit varying

• tsvector

• tsquery

• uuid

• xml

• json

• jsonb

• pg_Isn

• pg_snapshot

• txid_snapshot

• User Defined Composite Types

Table: Indicates the technique for retrieving and manipulating data within database tables

With Function: Indicates to perform conversion between two data types

Without Function: Indicates that the source type is binary-coercible to the target type without needing a function to handle the cast

With Inout: Indicates that the cast is an I/O conversion cast. This is handled by calling the output function of the source data type and passing the resulting string to the input function of the target data type.

As Assignment: Indicates that the cast can be automatically applied in assignment contexts

As Implicit: Indicates that the cast can be automatically applied in any context

Icu: Indicates the advanced, consistent, and locale-aware collation rules based on Unicode, suitable for diverse and multi-language sorting.

Libc: Indicates the collation rules based on the operating system's C library, with behavior that can vary and may be less flexible compared to ICU.

Specifies how name components are constructed using predefined parts.

Prime – Specifies the core noun or primary business term

Modifier 1 (M1) – Specifies the first qualifier describing the prime

Modifier 2 (M2) – Specifies the second qualifier providing additional context

Class (C) – Specifies the type or classification of the object

• P (Prime) – Indicates the primary business term

• M1 (Modifier 1) – Indicates the first descriptive modifier

• M2 (Modifier 2) – Indicates the second descriptive modifier

• C (Class) – Indicates the classification or type

Inherit: Specifies that properties are inherited from the parent

Override: Specifies that inherited properties can be modified

Harden: Specifies that inherited properties are fixed

Non-Identifying

Zero, One, or More: Specifies optional many relationship

One or More (P): Specifies mandatory many relationship

Zero or One (Z): Specifies optional one relationship

Restrict: Prevents deletion if dependent records exist

Cascade: Deletes dependent child records automatically

Set Null: Sets foreign key values to null

Set Default: Sets foreign key values to default

No Action: Defers constraint handling

Match Simple: Specifies simple matching of columns

Match Full: Specifies full matching across all columns

Match Partial: Specifies partial matching

Current_User: Returns the name of the currently logged-in database user. It represents the user identifier applicable for permission checking. Like current_role, it is normally equal to the session user but can be changed using SET ROLE.

Session_User: Returns the user's name who initiated the current database connection. Superusers can change this setting using SET SESSION AUTHORIZATION.

smallint: Requires 2 bytes of storage size and can store integers in the range of -32,768 to 32,767. It comes in handy for storing data like the age of people, the number of pages in a book, etc.

integer: Requires 4 bytes of storage space and can store integers from -2, 147, 483, 648 to 2, 147, 483, 647. It is useful for storing data such as a country's population, the number of active users on a social media app, etc.

bigint: Requires 8 bytes of storage size and can store integers in the range of -9, 223, 372, 036, 854, 775, 808 to +9, 223, 372, 036, 854, 775, 807.

Note: Using the BIGINT type consumes a lot of space and slows down the database, so you should have a good reason to do so.

It is useful for storing data such as the number of stars in a galaxy, scientific constants, etc.

Use a positive value to create an ascending sequence and a negative value to create a descending sequence. The default value is 1

TRUE: When the limit reaches, the next number is the minvalue or maxvalue.

FALSE: Any calls to nextval after the sequence has reached its maximum value will return an error.

Internal, c, Sql, User Defined: Specifies procedural languages of procedure

Definer: Indicates that it must use the privileges of the account that defined the procedure

Invoker: Indicates that the privileges of the account that invokes the procedure must be used

It can represent an internal procedure name, a path to an object file, an SQL command, or procedural language text.

Represents the name of the procedure in the C source code. If not specified, it defaults to the name of the SQL procedure being defined.

This expression must match the argument type of the parameter. Only input (including IN OUT) parameters can have a default value.

Classic Theme: Specifies standard visual styling

DARK Theme: Specifies a dark visual theme

Default Theme: Specifies the system default theme

Temporary: Temporary tables are tables that are visible and accessible only within the session in which you create them.

Global Temporary: The GLOBAL keyword is accepted for SQL-standard compatibility but has no effect in PostgreSQL. The table behaves as a standard temporary table that is visible only within the current session.

Local Temporary: The LOCAL keyword is accepted for SQL-standard compatibility but has no effect in PostgreSQL. The table behaves as a standard temporary table that is visible only within the current session.

Unlogged: Prioritize write speed by skipping data logging, enhancing write performance. However, this sacrifices data durability and replication, risking data loss in system failures.

Delete Rows: At the end of each transaction block, the temporary table automatically deletes all its rows. Essentially, it performs a TRUNCATE operation at each commit. However, this operation is not cascaded to the partitions of a partitioned table when used on one.

Preserve Rows: The default behavior is to take no special action at the end of transactions.

Drop: The temporary table will drop at the end of the current transaction block. When you use it on a partitioned table, this action drops its partitions, and when you use it on tables with inheritance children, it drops the dependent children.

Range: This type of partitioning assigns rows to partitions based on column values falling within a given range

List: The table is partitioned by explicitly listing which key value(s) appear in each partition.

Hash: To partition the table, you specify a modulus and a remainder for each partition. Each partition holds the rows where dividing the hash value of the partition key by the specified modulus produces the specified remainder

Name: Specifies the name of the table

Including Comments: Copies columns, constraints, and indexes.

Including Compression: Copies compression method of columns.

Including Constraints: Copies CHECK constraints from the original table to the new one. Also, it always copies not-null constraints to the new table.

Including Defaults: Copies default expressions for the copied column definitions.

Including Generated: Copies any generation expressions from the original column definitions to the new table. By default, the new columns will be standard base columns.

Including Identity: Copies identity specifications from the original column definitions to the new table. It creates a new sequence for each identity column in the new table, different from those linked to the old table.

Including Indexes: Copies indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints from the original table to the new table

Including Statistics: Copies extended statistics to the new table.

Including Storage: Copies the STORAGE settings for the copied column definitions.

Including All: Includes all available individual options

Excluding Comments: Removes columns, constraints, and indexes.

Excluding Compression: Removes compression method of columns.

Excluding Constraints: Removes CHECK constraints from the original table and moves them to the new one. Also, it always copies not-null constraints to the new table.

Excluding Defaults: Removes default expressions for the copied column definitions.

Excluding Generated: Removes any generation expressions from the original column definitions to the new table. By default, the new columns will be standard base columns.

Excluding Identity: Removes any identity specifications from the original column definitions to the new table. It creates a new sequence for each identity column in the new table, different from the sequences linked to the old table.

Excluding Indexes: Removes indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints from the original table to the new table.

Excluding Statistics: Removes extended statistics to the new table.

Excluding Storage: Removes the STORAGE settings for the copied column definitions.

Excluding All: Excludes all available individual options

With Data: Specifies whether the query should copy the produced data into the new table.

Provides a built-in editor to view and manage the SQL definition. The following options are available:

Fold All / Unfold All – Specifies options to collapse or expand the SQL content

Go to Line – Specifies navigation to a specific line in the script

Validate – Specifies validation of the SQL syntax

Read Only – Indicates whether the SQL editor is in read-only mode

SQL – Indicates whether editing mode is enabled

Inherit: Specifies to inherit the column physical name, physical data type, or null option setting

Override: Specifies to override inheritance for the column physical name, physical data type, or null option setting

Harden: Specifies to harden the column physical name

Null: Indicates that null values are allowed

Not Null: Indicates that null values are not allowed

Plain: Used for fixed-length values such as integers, which are in-line and uncompressed.

External: Used for external, uncompressed data

Extended: Used for external, compressed data

Main: Used for in-line, compressible data

Default: Sets the storage mode to the default mode for the column's data type

pglz: Uses a traditional, lightweight compression algorithm called pglz, which compresses column data when specified.

Iz4: Iz4 is a faster compression algorithm compared to pglz, offering a slightly lower compression ratio. It is known for its high speed and efficiency, and when specified, PostgreSQL compresses column data.

default: PostgreSQL defaults to use the pglz compression method for databases or tables, unless otherwise configured.

Always: Determine how explicitly user-specified values are handled in INSERT and UPDATE commands.

By Default: Determine how explicitly user-specified values are handled in INSERT and UPDATE commands.

True: When the limit reaches, the next number is the minvalue or maxvalue.

False: Any calls to nextval after the sequence has reached its maximum value will return an error

Note: This option is unavailable when a primary or foreign key index is selected.

Deferrable: Controls whether the index is deferrable

Not Deferrable: Controls whether the index is not deferrable

Initially Deferred: Indicates that the index should be checked only at the end of the transaction

Initially Immediate: Indicates that the index should be checked after each statement

Current_Role: Specifies the current role

Current_User: Specifies the user identifier used for permission checking. Usually, it equals the session user, but you can change it with SET ROLE.

Session_User: Specifies the user who initiated the current database connection; however, superusers can change this setting with SET SESSION AUTHORIZATION.

The directory should be empty and owned by the PostgreSQL system user. Specify the directory in the absolute path name format.

Select the schema from the drop-down list or click to create a new object

Base Type: Specifies that base types are fundamental scalar data types implemented at a low level, typically in a language like C. These base types serve as the building blocks for more complex data structures.

Note: Ensure that you have superuser access to create a new base type.

Enumerated Type: Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in several programming languages. An example of an enum type might be the days of the week or a set of status values for a piece of data.

Range Type: Allows you to represent a range of values for a specific underlying data type. These ranges are useful for scenarios where you need to express intervals or spans of values.

Composite Type: Allows you to define custom data structures with multiple fields. These fields can be any built-in or user-defined types, including other composite types.

Domain Type: Domain type builds upon an existing underlying type. It allows you to create a custom type with specific constraints.

Pseudo Type: The PostgreSQL type system contains several special-purpose entries collectively called pseudo-types. A pseudo-type cannot be used as a column data type but can be used to declare a function's argument or result type. Each of the available pseudo-types is useful when a function's behavior does not correspond to simply taking or returning a value of a specific SQL data type.

User-Defined

MinMax

Valid Values

Under Available Tables and Views, select the tables or views from which you want to select view columns and then, click Forward.

Note: This option is available only when you select a table or view in the From list. An alias is typically a new name that is more appropriate in the context of the view.

With Check Option: Indicates that all modifications made through the view to the underlying tables satisfy the definition of the view.

With Cascaded Check Option: Indicates that all underlying views undergo the check testing.

With Local Check Option: Indicates that the check testing is restricted only to the views being defined.

You can use the toolbar options to cut, copy, or paste text, open the Macro Toolbox, or work with the Text Editor.

Database Authentication: Indicates that database access credentials are used to establish a connection for reverse engineering.

Enter an owner name to the Global Override object to override all assignments for the listed object groups.
The list of available objects is based on the current target server and may include the following object groups: Table, Index, View, and Materialized View.

Physical Names: Indicates that only physical names of the tables are included in the generated schema.

Physical Names, show owner: Indicates that physical names and owners of the tables are included in the generated schema.

Database Authentication: Indicates that database access credentials are used to establish a connection for forward engineering.